package gbench.sandbox.jdbc;

import org.junit.jupiter.api.Test;

import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.Stream;

import gbench.common.tree.LittleTree;
import gbench.common.tree.LittleTree.IRecord;
import gbench.common.tree.LittleTree.Jdbc.IJdbcSession;
import gbench.common.tree.LittleTree.SQL;
import gbench.commonApp.data.DataApp;
import lombok.AllArgsConstructor;
import lombok.Data;

import static gbench.common.tree.LittleTree.IRecord.*;
import static gbench.common.tree.LittleTree.SQL.nsql;
import static gbench.common.tree.LittleTree.*;

/**
 * 数据是一种结构，概念是一种结构，结构是条理化这个世界的基本手段，是系统化的基石
 * @author gbench
 *
 */
public class JunitStruct {
    /**
     * 
     * 
     * @author gbench
     *
     */
    public class MyDataApp extends DataApp {

        /**
         * 
         * @param name 数据库名称
         */
        public MyDataApp(String dbname) {
            this.dbname = dbname;
            this.reload(this.configureDbName(dbname));
        }

        /**
         * 数据查询
         * 
         * @param sql sql 语句
         * @return IRecord 集合
         */
        public List<IRecord> sqlquery(final String sql, String dbname) {
            reload(this.configureDbName(dbname));
            return this.jdbc.sql2records(sql);
        }

        /**
         * 数据查询
         * 
         * @param sql sql 语句
         * @return IRecord 集合
         */
        public List<IRecord> sqlquery(final String sql) {
            return this.sqlquery(sql, dbname);
        }
        
        /**
         * 数据查询
         * 
         * @param sql sql 语句
         * @return IRecord 集合
         */
        public void sqlexecute(final String sql) {
            this.jdbc.sqlexecute(sql);
        }

        /**
         * 配置数据库
         * 
         * @param dbname
         * @return 设定数据库
         */
        public IRecord configureDbName(final String dbname) {
            return cfg.derive("url", MFT("jdbc:mysql://localhost:3306/{0}?serverTimezone=GMT%2B8", dbname));
        }

        private String dbname; // 数据库名称

    }
    
    /**
     * SQL FurnaceBuilder
     * @author gbench
     *
     */
    public interface SB{
        public SQL get(Object ... objects ); // 生成一个SQL对象
    }
    
    /**
     * 生成一个对象结构
     * 
     * @param name 结构名称
     * @param rec 属性集合
     * @return 对象结构
     */
    public static IRecord struct(String name,IRecord rec) {
        final var builder = IRecord.builder(rec);
        return REC(
            "name",name,
            "recbuilder",builder,
            "sql",new SQL(name,rec),
            "SB",(SB)(aa->SQL.of(name,A2REC(aa).keys(rec.keys().toArray(String[]::new)))
        ));
    }
    
    /**
     * buildTable
     */
    public void createSqlTable(final IRecord concept) {
        final var sqls = concept.findOne(SQL.class).createTable();
        dataApp.withTransaction( sess -> {
            if(dataApp.getDataBase().exists(concept.str("name"))) sess.sqlexecute(sqls.get(0));
            for(var sql:Tuple2.split(sqls, 1)._2())sess.sqlexecute(sql);
        });
    }

    @Test
    public void user() {
        final var concept = struct("user", // 概念是一种结构
            EG(Integer.class,String.class,String.class,LocalDateTime.class) // 值类型
            .keys("id,32name,128address,birth")); // 属性名
        concept.findOne(SQL.class).createTable().forEach(System.out::println);// 生成一个创建表的SQL
        createSqlTable(concept);
        Stream.of(A(1,"李四","北京","1982-06-11"),A(1,"李四","北京","1982-06-11")) // 输入数据
        .map(aa->concept.findOne2(SB.class,e->e.get((Object[])aa).insert()))
        .forEach(dataApp::sqlexecute);
    }
    
    @Test
    public void product() {
        final var concept = struct("t_product", // 概念是一种结构
            EG(Integer.class,String.class,String.class,LocalDateTime.class) // 值类型
            .keys("id,32name,128origin,mfd")); // 属性名
        concept.findOne(SQL.class).createTable().forEach(System.out::println);// 生成一个创建表的SQL
        createSqlTable(concept);
        Stream.of(A(1,"苹果","北京","1982-06-11"),A(2,"哈密瓜","北京","1982-06-11")) // 输入数据
        .map(aa->concept.findOne2(SB.class,e->e.get((Object[])aa).insert()))
        .forEach(dataApp::sqlexecute);
    }
    
    @Test
    public void order() {
        final var concept = struct("t_orderline", // 概念是一种结构
            EG(Integer.class,Integer.class,LocalDateTime.class,Double.class,Double.class) // 值类型
            .keys("userid,productid,createtime,price,amount")); // 属性名
        concept.findOne(SQL.class).createTable().forEach(System.out::println);// 生成一个创建表的SQL
        createSqlTable(concept);
        Stream.of(A(1,1,"1982-06-11",5.8),A(1,1,"1992-06-11",7.9)) // 输入数据
        .map(aa->concept.findOne2(SB.class,e->e.get((Object[])aa).insert()))
        .forEach(dataApp::sqlexecute);
    }
    
    @Documented 
    @Retention(value = RetentionPolicy.RUNTIME) 
    @Target(ElementType.TYPE) 
    @interface Table{
        public String[] primaryKey() default {"id"};
        public String table() default "";
        public String[] foreignKeys() default {};
    }
    
    /**
     * 提取注解
     */
    public static<T> Table tblAnno(Class<T> clazz){
        final var anno = LittleTree.ANNO(clazz, Table.class);
        return anno;
    }
    
    /**
     * 注意一定需要一个 static class
     * @author gbench
     *
     */
    @Data @Table(table="t_user")
    static class User{
        public String name;
    }
    
    @Data @Table(table="t_product")
    static class Product{
        public String name;
    }
    
    @Data @Table(table="t_orderline",foreignKeys= {"userid","productid"})
    static class OrderLine{
        public String name;
    }
    
    /**
     * 通过 PivotData 而创建数据模型 的 模型工厂
     * @author gbench
     *
     */
    public static class ModelsFactory{
        
        public ModelsFactory(final IRecord pivotData) {
            this.pivotData = pivotData;
        }
        
        /**
         * 获取一个数据：哪怕他是一个List结构
         * @param <T>
         * @param obj
         * @return
         */
        @SuppressWarnings("unchecked")
        public static <T> T justone(final Object obj) {
            return obj instanceof Collection?((Collection<T>)obj).iterator().next():(T) obj;
        }
        
        /**
         * 获取一条轴线路径
         * @param path 轴线路径
         * @return 轴线路径结构
         */
        public IRecord getPoint(final String path) {
            return pivotData.path2target(path,ModelsFactory::justone);
        }
        
        /**
         * 数据熔炉
         * @author gbench
         *
         */
        @Data @AllArgsConstructor
        public class DataModeler {
            
            private String currentTable;
            private IJdbcSession<UUID, Object> sess;
            private final IRecord data;// 被代理的对象
            
            @SuppressWarnings("unchecked")
            public <T> T model(final Class<T> myClazz) {
                return (T)Proxy.newProxyInstance(this.getClass().getClassLoader(), new Class[] {myClazz}, 
                    new InvocationHandler() {
                        @Override
                        public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
                            switch (method.getName()) {
                                case "get": {
                                    if(args.length==2 && args[0]!=null) {
                                        final var key = (String)args[0];
                                        final var clazz = (Class<?>)args[1];
                                        final var anno = tblAnno(clazz);
                                        if(anno!=null) {
                                            final var path = MFT("{0}/{1}/{2}",DataModeler.this.currentTable,key,anno.table());
                                            final IRecord meta = ModelsFactory.this.pivotData.path2target(path,ModelsFactory::justone);
                                            final var value = data.get(key,sess.ftblget(meta.str(2),meta.str(3))); // 对 usrid进行展开
                                            return value.mutate(clazz);// 转换成目标类型
                                        } else {
                                            return data.get(key,clazz);
                                        }//if
                                    }else {
                                        return null;
                                    }//if
                                }//case get
                                default: {
                                    return null;
                                }// default
                            }// switch
                        }// invoke
                });// newProxyInstance
            }
            
            public DataModel model() {
                return this.model(DataModel.class);
            }
            
            /**
             * 把字段名称key用tclass展开 
             * @param <T>
             * @param key 字段名
             * @param tclass 类型
             * @return IRecord
             */
            public IRecord expand(String key,Class<?> tclass) {
                final var path = MFT("{0}/{1}/{2}",this.currentTable,key,tblAnno(tclass).table());
                final IRecord meta = pivotData.path2target(path,ModelsFactory::justone);
                data.compute(key,sess.ftblget(meta.str(2),meta.str(3))); // 对 usrid进行展开
                return data;
            }
            
            /**
             * 数据展开<br>
             * 把数据用在 tclasses所定义的维度中进行展开 <br> 
             * @param tclasses 展开维度的概念控件
             * @return 数据展开后的结果
             */
            @SafeVarargs
            public final IRecord expand(Class<?> ...tclasses) {
                return Stream.of(tclasses)
                    .map(clazz->this.expand(MFT("{0}id",clazz.getSimpleName().toLowerCase()),clazz))
                    .collect(Collectors.toList()).get(0);
            }
        }
        
        @Data @AllArgsConstructor
        public class ModlerBuilder {
            
            private final String currentTable;
            private final IJdbcSession<UUID, Object> sess;
            
            /**
             * 创建一个 数据模型
             * @param rec
             * @return 创建一个数据代理
             */
            public DataModeler get(final IRecord rec) {
                return new DataModeler(currentTable,sess,rec);
            }
            
        }
        
        /**
         * 创建一数据代理
         * @param name 当前表名
         * @param sess sqlSession 对话
         * @return FurnaceBuilder
         */
        public ModlerBuilder builder( final String currentTable, final IJdbcSession<UUID, Object> sess ) {
            return new ModlerBuilder( currentTable, sess );
        }
        
        private IRecord pivotData;
    }
    
    /**
     * 获取枢轴数据
     * @param clazzes
     * @return
     */
    public Stream<IRecord> getPivotData(Class<?>... clazzes) {
        return Stream.of(clazzes).flatMap(clazz -> Stream.of(tblAnno(clazz))// clazz 
            .flatMap(tblinfo -> Elem.cph2(tblinfo.foreignKeys(),tblinfo.primaryKey()) //  tblinfo
                .map(IRecord::L2REC)// 转换成IRecord
                .map(e->e.keys("fk,pk").add("tbl",tblinfo.table())) // 生成结果IRecord    
            ));
    }
    
    /**
     * 数据模型
     * @author gbench
     *
     */
    interface DataModel{
        public <T> T get(String name,Class<T> clazz);
    }
    
    /**
     * 根据表名提取 数据模版
     * @param table 表名
     * @param s1 字段名序列
     * @param s2 字段名别名序列
     * @return 数据插入模版
     */
    public String inserttpl(final String table,String s1,String s2) {
        final String inserttpl = "insert into " + (table==null?"t_tbl":table) + " (" + s1 + ") values ${ foreach e in entries (" + s2 + ") }";
        return inserttpl;
    }
    
    /**
     * 初始化一张表
     * 
     * @param tblname 表名
     * @param proto   原型结构
     * @param entries    行数据,需要每个列都包含有效数据
     * @param tbldefs 表结构定义
     */
    public void initTable(final String tblname, final IRecord proto, final IRecord ... entries) {
        final var keys = proto.aoks2rec(compose_f(e->e.str("name"),SQL::parseFieldName)).filter2("id").keys(); // 提取键名列表 
        final var s1 = JOIN( keys, "," ); // 设置键名
        final var s2 = JOIN( keys.stream().map(e -> "e." + e), "," ); // 键名
        final var sb = SQL.builder(tblname); // 狗仔entry sql 系列构建器具
        final var coldefs = proto.keys().stream().map(e->e.replace("$", "")).collect(Collectors.toList());
        final var createsqls = sb.get(proto.keys(coldefs)).createTable(true); // 生成createsqls
        dataApp.withTransaction(sess -> {
            TUPLES(createsqls).filter(t -> t._1() != 1).map(t -> t._2()).peek(System.out::println).forEach(trycatch2(sess::sqlexecute));
            Stream.of(nsql(inserttpl(tblname,s1,s2),REC("entries",entries))).forEach(trycatch2(sess::sqlexecute)); // 插入数据
        });// 创建数据表
    }
    
    /**
     * 数据库准备
     */
    @Test
    public void init() {
        final var path = "http://localhost:8089/widget/api//baas/jasminedata/file/read?path=D:/sliced/ws/gitws/klc/upload/高斯.jpg";
        
        //初始化用户表
        this.initTable("t_user", REC("#id",Integer.class,"~12name",String.class,"2sex",String.class,"64address",String.class,
            "birth",LocalDateTime.now(),"64title",String.class,"128path",String.class),
            A(  REC("name","张三","sex","男","address","上海","birth",LocalDateTime.now(),"path",path),
                REC("name","李四")
        ));
        
        // 初始化产品表
        this.initTable("t_product", REC("#id",Integer.class,"name",String.class,"origin",String.class,"createtime",LocalDateTime.now()),
                A(  REC("name","苹果","origin","北京","createtime",LocalDateTime.now()),
                    REC("name","鸭梨","origin","北京","createtime",LocalDateTime.now())
        ));
        
        // 初始化订单项目表
        this.initTable("t_orderline", REC("~userid",Integer.class,"~productid",Integer.class,"createtime",LocalDateTime.now(),"price",Double.class,"quantity",Double.class),
                A(  REC("userid",1,"productid",1,"createtime",LocalDateTime.now(),"price",5.8,"quantity",1000),
                    REC("userid",1,"productid",2,"createtime",LocalDateTime.now(),"price",7.9,"quantity",1000)
        ));
        
        // 数据查询
        dataApp.withTransaction(sess->{
            sess.sql2records("select * from t_user").forEach(System.out::println);
            sess.sql2records("select * from t_product").forEach(System.out::println);
            sess.sql2records("select * from t_orderline").forEach(System.out::println);
        });
    }

    /**
     * 演示示例
     */
    @Test
    public void foo() {
        
        final var pivotData = Stream.of(// 枢轴数据
            "t_orderline/userid/t_user/id", // t_orderline <-> t_user
            "t_orderline/productid/t_product/id" // t_orderline <-> t_product
        ).map(IRecord::STRING2REC).collect(IRecord.pvtclc(0,1,2));// 外建键关系。
        
        final var pdh = new ModelsFactory(pivotData);// 数据表
        System.out.println(pdh.getPoint("t_orderline/userid/t_user"));
        
        dataApp.withTransaction(sess -> {
            final var mbuilder = pdh.builder("t_orderline",sess);
            sess.sql2records("select * from t_orderline").stream()
            //.map(mbuilder::get).map(p->p.expand(User.class,Product.class)) // 把数据在 对象指定维度进行展开
            .forEach(e -> {
                
                final var model = mbuilder.get(e).model();// 获取数据项
                final var user = model.get("userid",User.class);
                final var pct = model.get("productid",Product.class);
                System.out.println("模型获取的价格price :"+model.get("price", Double.class));
                
                // 设置对象字段
                e.set("user", user); e.set("product", pct);
                
                final var path = e.path2obj("userid/name");
                System.out.println("path:"+path);
                System.out.println("\ntranverse:");
                e.dfs_forone((k,obj)->System.out.println(k+"-->"+obj));
            });// forEach
        });// withTransaction
    }
    
    @Test
    public void bar() {
        getPivotData(OrderLine.class).forEach(e->{
            System.out.println(e);
        });
    }

    final MyDataApp dataApp = new MyDataApp("db1");
}

